package day17;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

public class jdbcTest {
	@Test
	public void test() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java1807", "root", "root");
			String sql = "select id,name,age,gender from student";
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(sql);
			List<Student> list = new ArrayList<>();
			while (resultSet.next()) {
				Integer id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				Integer age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Test
	public void test2() {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JdbcUtil.getconnection();
			String sql = "select id,name,age,gender from student;";
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			List<Student> list = new ArrayList<>();
			while (resultSet.next()) {
				Integer id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				Integer age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testUpdate() {
		Connection connection = null;
		Statement statement = null;
		//获得连接对象connection
		try {
			connection = JdbcUtil.getconnection();
			//写sql语句
			//String sql = "update student set age= age+8 where id = 1;";
			//String sql = "insert into student(name,age,gender) values ('six',24,'男')";
			String sql = "delete from student where id=10;";
			//创建statement
			statement = connection.createStatement();
			//执行sql语句
			int count = statement.executeUpdate(sql);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//关闭连接
			JdbcUtil.close(connection, statement, null);
		}
		
	}
}
